package pagination;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import control.database.DataBase;

public class Usercall_sql {

public String getTotalRow(String section,String begintime,String endtime,String username,String domainName,String username_type){
    String sql="select count(*)as total from usercalloutrecord a,users b where a.userid like '"+section+"%' and a.userid=b.username and a.domainName=b.domainName  and lasttime>='"+begintime+"' and stoptime<='"+endtime+"' and a.domainname='"+domainName+"'";
    if(!username.equals("")){
    sql+=" and a.userid='"+username+"'";
    }
    if(!username_type.equals("10")){
    	sql+="and user_type='"+username_type+"'";
    }
	return sql;
}
public String pagination(int cPage, int pageSize,String username,String domainName,String begintime,String endtime,String section,String username_type){
	
	
	String sql="select top "+pageSize+" * from usercalloutrecord where userid like '"+section+"%'and domainname='"+domainName+"' and lasttime>='"+begintime+"' and stoptime<='"+endtime+"'";
	 if(!username.equals("")){
		    sql+=" and userid='"+username+"' ";
		    }
	 else if(!username_type.equals("10")){
		 sql+=" and userid in(select username from users where  domainName='"+domainName+"' and user_type='"+username_type+"')";
	 }
	if(cPage>1)
	{
		sql="select top "+pageSize+" * from usercalloutrecord where ID>(select max(ID) from (select top "+(cPage-1)*pageSize+" * from  usercalloutrecord where userid like '"+section+"%' and domainname='"+domainName+"' and lasttime>='"+begintime+"' and stoptime<='"+endtime+"' order by ID) as T) and userid like '"+section+"%' and domainname='"+domainName+"' and lasttime>='"+begintime+"' and stoptime<='"+endtime+"' ";		
		if(!username.equals(""))
		{
			
        sql+=" and userid='"+username+"'";	
		}
		 if(!username_type.equals("10")){
			 sql+=" and userid in(select username from users where  domainName='"+domainName+"' and user_type='"+username_type+"')";		
				
		 }

	}

return sql+" order by ID";
}
	public static void main(String[] args) {
		System.out.println(new Usercall_sql().getTotalRow("0","2006-09-01","2006-09-12","02412345672","m10060.com","PHS"));
		System.out.println(new Usercall_sql().pagination(2,2,"02412345672","10060.com","2006-06-01","2006-08-15","0","PHS"));

	}

}
